#!/usr/bin/python2.7
#coding=utf8

import sys
import datetime
import os
def parsing(filename):
    #读取CSV文件,一行一行解析
    csvfileparentpath=getcsvfileparentpath()
    csvfilepath=csvfileparentpath+"\\"+filename
    #print(csvfilepath)
    databasename=getDataBasename().split(" ")
    #print(databasename)
    for i in databasename:
        if i == "oracle":
            parse_oracle(csvfilepath)
        elif i == "sqlalchemy_oracle":
            parse_sqlalchemy_oracle(csvfilepath)
        elif i == "mysql":
            #print("here")
            parse_mysql(csvfilepath)
        elif i == "sqlserver":
            #print("here")
            parse_sqlserver(csvfilepath)
        else :
            print("Error No such databasename ")
            sys.exit(1)
    
    
    
    
    
        
    
    
    
    
    
def getDataBasename():
    from conf.globalconfiguration import Config
    return Config().DATABASENAME
    
    
def getcsvfilepath():
    from conf.globalconfiguration import Config
    return Config().CSVFILEPATH
    
    
def getcsvfileparentpath():
    from conf.globalconfiguration import Config
    return Config().CSVFILEPARENTPATH
    
    
    
def parse_oracle(csvfilepath):
    from conf.globalconfiguration import Config
    obj=open(csvfilepath,"rb")
    sqlfilepath=Config.ORACLESQLFILEPATH
    sqlfile=open(sqlfilepath,"a")
    tablename=""
    #print(sqlfile)
    for line in obj:
        line=line.decode(Config.CSVFILECODING).encode("utf8")
        fields=line.split(",")
        if fields[0] == "tablename":
            tablename=fields[1]
            sqlfile.write("create table "+tablename+" (\n")
        elif fields[0] == "column":
            from database.oracle.conf import Dataformat
            dataformat=Dataformat()
            if fields[4] == u"是".encode('utf8'):
                #print("是")
                sqlfile.write(fields[1]+" "+dataformat.transfer(fields[3])+",\n")
            else :
                #print("否")
                sqlfile.write(fields[1]+" "+dataformat.transfer(fields[3])+" not null,\n")
        elif fields[0] == "primarykey":
            sqlfile.write("constraint pk_%s primary key (%s) ,\n" % (tablename,fields[1]))
        elif fields[0] == "restrict":
            #print(fields[1])
            restrict_keys=fields[1]
            #print(restrict_keys)
            src=u'、'.encode("utf8")
            #print restrict_keys.find(src)
            restrict_keys=restrict_keys.replace(src,",")
            sqlfile.write("constraint %s_unique unique(%s) \n" % (tablename,restrict_keys))
        elif fields[0] == "end":
            sqlfile.write(");\n")
        elif fields[0] == "stop":
            sqlfile.write("\n")
            sqlfile.close()
            break
        else :
            print("ERROR! csvfile format had error")
            sys.exit(1)
    obj.close()
    
def parse_sqlalchemy_oracle(csvfilepath):
    from conf.globalconfiguration import Config
    from database.sqlalchemy_oracle.conf import Dataformat
    obj=open(csvfilepath,"rb")
    dataformat=Dataformat()
    sqlfilepath=Config.SQLALCHEMY_ORACLESQLFILEPATH
    sqlfile=open(sqlfilepath,"a")
    #sqlfile.write(dataformat.sqlalchemy_head)
    tablename=""
    #print(sqlfilepath)
    for line in obj:
        line=line.decode(Config.CSVFILECODING).encode("utf8")
        fields=line.split(",")
        if fields[0] == "tablename":
            tablename=fields[1]
            sqlfile.write("class %s (Base):\n" % tablename.title())
            sqlfile.write('    __tablename__="%s" \n' % (tablename.upper()))
        elif fields[0] == "column":
            if fields[1] == "id":
                sqlfile.write('    %s = Column(%s, Sequence("seq_%s_id"),primary_key=True)\n' % (fields[1],dataformat.transfer(fields[3]),tablename.lower()))
            elif (fields[1] == "create_time") or (fields[1] =="update_time"):
                if fields[4] == u"是".encode('utf8'):
                    sqlfile.write("    %s = Column(%s, nullable=%s ,default=datetime.datetime.now())\n" % (fields[1],dataformat.transfer(fields[3]),"True"))
                else :
                    sqlfile.write("    %s = Column(%s, nullable=%s ,default=datetime.datetime.now())\n" % (fields[1],dataformat.transfer(fields[3]),"False"))
            else :
                if fields[4] == u"是".encode('utf8'):
                    sqlfile.write("    %s = Column(%s, nullable=%s)\n" % (fields[1],dataformat.transfer(fields[3]),"True"))
                else :
                    sqlfile.write("    %s = Column(%s, nullable=%s)\n" % (fields[1],dataformat.transfer(fields[3]),"False"))
        elif fields[0] == "primarykey":
            pass
            #sqlfile.write("    constraint pk_%s primary key (%s) ,\n" % (tablename,fields[1]))
        elif fields[0] == "restrict":
            restrict_keys=fields[1]
            src=u'、'.encode("utf8")
            restrict_keys=restrict_keys.replace(src,",")
            restrict_keys=restrict_keys.split(",")
            res=[]
            for i in range(len(restrict_keys)):
                res.append(tablename.title()+"."+restrict_keys[i])
            res_str=",".join(res)
            sqlfile.write("Index('data_%s_unique', %s, unique=True)\n" % (tablename,res_str))
        elif fields[0] == "end":
            sqlfile.write("\n\n\n")
        elif fields[0] == "stop":
            sqlfile.write("\n")
            sqlfile.close()
            break
        else :
            print("ERROR! csvfile format had error")
            sys.exit(1)
    obj.close()
    
    
def parse_mysql(csvfilepath):
    from conf.globalconfiguration import Config
    from database.mysql.conf import Dataformat
    obj=open(csvfilepath,"rb")
    dataformat=Dataformat()
    sqlfilepath=Config.MYSQL_FILEPATH
    sqlfile=open(sqlfilepath,"w")
    tablename=""
    for line in obj:
        line=line.decode(Config.CSVFILECODING).encode("utf8")
        fields=line.split(",")
        if fields[0] == "tablename":
            tablename=fields[1]
            sqlfile.write("create table %s (\n" % (tablename) )
        elif fields[0] == "column":
            if fields[1] == "id":
                sqlfile.write('%s %s not null AUTO_INCREMENT ,\n' % (fields[1],dataformat.transfer(fields[3])))
            elif (fields[1] == "create_time") or (fields[1] =="update_time"):
                if fields[4] == u"是".encode('utf8'):
                    #print(str(datetime.datetime.now()).split(" ")[0])
                    aa="%s %s  default '"+str(datetime.datetime.now()).split(" ")[0]+"',\n"
                    sqlfile.write(aa % (fields[1],dataformat.transfer(fields[3])))
                else :
                    #print(str(datetime.datetime.now()).split(" ")[0])
                    aa="%s %s not null default '"+str(datetime.datetime.now()).split(" ")[0]+"', \n"
                    sqlfile.write(aa % (fields[1],dataformat.transfer(fields[3])))
            else :
                if fields[4] == u"是".encode('utf8'):
                    sqlfile.write("%s %s ,\n" % (fields[1],dataformat.transfer(fields[3])))
                else:
                    sqlfile.write("%s %s not null ,\n" % (fields[1],dataformat.transfer(fields[3])))
        elif fields[0] == "primarykey":
            sqlfile.write("primary key (%s) ,\n" % (fields[1]))
            #sqlfile.write("    constraint pk_%s primary key (%s) ,\n" % (tablename,fields[1]))
        elif fields[0] == "restrict":
            restrict_keys=fields[1]
            src=u'、'.encode("utf8")
            restrict_keys=restrict_keys.replace(src,",")
            restrict_keys=restrict_keys.split(",")
            res=[]
            for i in range(len(restrict_keys)):
                res.append(restrict_keys[i])
            res_str=",".join(res)
            sqlfile.write("key data_%s_unique  (%s)\n" % (tablename,res_str))
        elif fields[0] == "end":
            sqlfile.write(') ENGINE=MyISAM  DEFAULT CHARSET=utf8;\n\n\n')
        elif fields[0] == "stop":
            sqlfile.write("\n")
            sqlfile.close()
            break
        else :
            print("ERROR! csvfile format had error")
            sys.exit(1)
    obj.close()
    
    
    
    
def parse_sqlserver(csvfilepath):
    from conf.globalconfiguration import Config
    from database.sqlserver.conf import Dataformat
    obj=open(csvfilepath,"rb")
    dataformat=Dataformat()
    sqlfilepath=Config.SQLSERVER_FILEPATH
    sqlfile=open(sqlfilepath,"w")
    tablename=""
    res_str=""
    for line in obj:
        line=line.decode(Config.CSVFILECODING).encode("utf8")
        fields=line.split(",")
        if fields[0] == "tablename":
            tablename=fields[1]
            sqlfile.write("create table %s (\n" % (tablename) )
        elif fields[0] == "column":
            if fields[1] == "id":
                sqlfile.write('%s %s identity(1,1) constraint %s_pkid primary key (%s) ,\n' % (fields[1],dataformat.transfer(fields[3]),tablename,fields[1]))
            elif fields[1] == "create_time":
                if fields[4] == u"是".encode('utf8'):
                    #print(str(datetime.datetime.now()).split(" ")[0])
                    aa="%s %s  default '"+str(datetime.datetime.now()).split(" ")[0]+"',\n"
                    sqlfile.write(aa % (fields[1],dataformat.transfer(fields[3])))
                else :
                    #print(str(datetime.datetime.now()).split(" ")[0])
                    aa="%s %s not null default '"+str(datetime.datetime.now()).split(" ")[0]+"', \n"
                    sqlfile.write(aa % (fields[1],dataformat.transfer(fields[3])))
            elif fields[1] =="update_time":
                if fields[4] == u"是".encode('utf8'):
                    #print(str(datetime.datetime.now()).split(" ")[0])
                    aa="%s %s  default '"+str(datetime.datetime.now()).split(" ")[0]+"'\n"
                    sqlfile.write(aa % (fields[1],dataformat.transfer(fields[3])))
                else :
                    #print(str(datetime.datetime.now()).split(" ")[0])
                    aa="%s %s not null default '"+str(datetime.datetime.now()).split(" ")[0]+"' \n"
                    sqlfile.write(aa % (fields[1],dataformat.transfer(fields[3])))
            else :
                if fields[4] == u"是".encode('utf8'):
                    sqlfile.write("%s %s ,\n" % (fields[1],dataformat.transfer(fields[3])))
                else:
                    sqlfile.write("%s %s not null ,\n" % (fields[1],dataformat.transfer(fields[3])))
        elif fields[0] == "primarykey":
            pass
            #sqlfile.write("primary key (%s) ,\n" % (fields[1]))
            #sqlfile.write("    constraint pk_%s primary key (%s) ,\n" % (tablename,fields[1]))
        elif fields[0] == "restrict":
            restrict_keys=fields[1]
            src=u'、'.encode("utf8")
            restrict_keys=restrict_keys.replace(src,",")
            restrict_keys=restrict_keys.split(",")
            res=[]
            for i in range(len(restrict_keys)):
                res.append(restrict_keys[i])
            res_str=",".join(res)
            #sqlfile.write("key data_%s_unique  (%s)\n" % (tablename,res_str))
        elif fields[0] == "end":
            sqlfile.write(");\ncreate unique index %s_unique on %s(%s);\n\n\n"  % (tablename,tablename,res_str))
            #sqlfile.write(') ENGINE=MyISAM  DEFAULT CHARSET=utf8;\n\n\n')
        elif fields[0] == "stop":
            sqlfile.write("\n")
            sqlfile.close()
            break
        else :
            print("ERROR! csvfile format had error")
            sys.exit(1)
    obj.close()    